package DatabasePackage;

import java.sql.Blob;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;

import javax.sql.rowset.serial.SerialBlob;
import javax.sql.rowset.serial.SerialException;

import BusinessLogic.User;

/**
 * This is the schema for user
 */
public class UserDBTable extends DBtable {
  public enum UserDBTScheme {
    USER_NAME("varchar(255)"), PASSWORD("varchar(255)"), FIRST_NAME("varchar(255)"), LAST_NAME("varchar(255)"), 
    EMAIL("varchar(255)"), PHOTO("BLOB"),ROLE("varchar(10)"),PHONE("varchar(30)");
    private final String COLUMN_TYPE;
    
    UserDBTScheme(String type) {
      COLUMN_TYPE = type;
    }

    String getType() {
      return COLUMN_TYPE;
    }
  }
  
  public UserDBTable() {
    super(new HashMap<String, String>(), new LinkedList<String>(), new LinkedList<String>());
    // Initializing the keys
    keys.add(UserDBTScheme.USER_NAME.toString());
    // Initializing the scheme
    for (UserDBTScheme column : UserDBTScheme.values())
      scheme.put(column.toString(), column.getType());
  }
  
  /**
   * @param username
   *          the name of the user you wish to get the details of
   * @return returns a list of the fields of the selected user if it exists, if
   *         he doesn't throws an exception
   * @throws SQLException
   *           if there was problem connecting to the sql database or there was
   *           a database error
   * @throws ClassNotFoundException
   *           if connector/j was not linked
   * @throws ItemNotFoundException
   *           if there is no user with the given username
   */
  public User getUser(Connection con, String name) throws SQLException, ClassNotFoundException {
    Map<String, byte[]> conditions = new HashMap<String, byte[]>();
    conditions.put(UserDBTScheme.USER_NAME.toString(), name.getBytes());
    List<Map<String, byte[]>> user = getItem(con, conditions);
    if (user.isEmpty()) return null;
    return createUser(user.get(0));
  }
  
  /**
   * @param username
   *          the name of the user you wish to get the details of
   * @return returns a list of the fields of the selected user if it exists, if
   *         he doesn't throws an exception
   * @throws SQLException
   *           if there was problem connecting to the sql database or there was
   *           a database error
   * @throws ClassNotFoundException
   *           if connector/j was not linked
   * @throws ItemNotFoundException
   *           if there is no user with the given username
   */
  public List<User> getAllUsers(Connection con) throws ItemNotFoundException, SQLException, ClassNotFoundException {
    List<User> userList = new LinkedList<User>();
	  Map<String, byte[]> conditions = new HashMap<String, byte[]>();
    for(Map<String, byte[]> userrecord : getItem(con, conditions))
    {
    	userList.add(createUser(userrecord));
    }
    return userList;
  }
  
  private User createUser(Map<String, byte[]> u) throws SerialException, SQLException {
    Blob photo = null;
    String name = new String(u.get(UserDBTScheme.USER_NAME.toString()));
    String pass = new String(u.get(UserDBTScheme.PASSWORD.toString()));
    String first_name = new String(u.get(UserDBTScheme.FIRST_NAME.toString()));
    String last_name = new String(u.get(UserDBTScheme.LAST_NAME.toString()));
    String email = new String(u.get(UserDBTScheme.EMAIL.toString()));
    String phone = new String(u.get(UserDBTScheme.PHONE.toString()));
    if(u.get(UserDBTScheme.PHOTO.toString()) != null){
      photo = new SerialBlob(u.get(UserDBTScheme.PHOTO.toString()));
    }
    String role = new String(u.get(UserDBTScheme.ROLE.toString()));
    return new User(name,pass,first_name,last_name,email,photo,role,phone); 
  }

  /**
   * Deletes the given user from the database, if he doesn't exist, throws an
   * exception
   * 
   * @param name
   *          the username of the user to be deleted
   * @throws ItemNotFoundException
   *           if there is no user with the given username
   * @throws SQLException
   *           if there was problem connecting to the sql database or there was
   *           a database error
   * @throws ClassNotFoundException
   *           if connector/j was not linked
   */
  public void deleteUser(Connection con, String name) throws ItemNotFoundException, SQLException, ClassNotFoundException {
    Map<String, byte[]> conditions = new HashMap<String, byte[]>();
    conditions.put(UserDBTScheme.USER_NAME.toString(), name.getBytes());
    removeItem(con, conditions);
  }
  
  /**
   * Adds the given user to the database, if he does exist, throws an exception
   * 
   * @param u
   *          the user record to insert
   * @throws ItemAlreadyInDBException
   *           if there is already a user with the given username in the
   *           database
   * @throws SQLException
   *           if there was problem connecting to the sql database or there was
   *           a database error
   * @throws ClassNotFoundException
   *           if connector/j was not linked
   */
  public void insertUser(Connection con,User u) throws ItemAlreadyInDBException, SQLException, ClassNotFoundException {
	  Map<String, byte[]> record = new HashMap<String, byte[]>();
	  record.put(UserDBTScheme.EMAIL.toString(),u.getEmail().getBytes());
	  record.put(UserDBTScheme.FIRST_NAME.toString(),u.getFirst_name().getBytes());
	  record.put(UserDBTScheme.LAST_NAME.toString(),u.getLast_name().getBytes());
	  record.put(UserDBTScheme.PASSWORD.toString(),u.getPass().getBytes());
	  record.put(UserDBTScheme.PHONE.toString(),u.getPhone().getBytes());
	  if(u.getPhoto() != null){
	    long len = u.getPhoto().length();
	    record.put(UserDBTScheme.PHOTO.toString(),u.getPhoto().getBytes(1, (int)len));
	  }
	  else{
	    record.put(UserDBTScheme.PHOTO.toString(),null);
	  }
	  record.put(UserDBTScheme.ROLE.toString(),u.getRole().getBytes());
	  record.put(UserDBTScheme.USER_NAME.toString(),u.getName().getBytes());
	  insertItem(con, record);
  }
  
  public void updateUser(Connection con,User u) throws ItemAlreadyInDBException, SQLException, ClassNotFoundException {
	  Map<String, byte[]> record = new HashMap<String, byte[]>();
	  record.put(UserDBTScheme.EMAIL.toString(),u.getEmail().getBytes());
	  record.put(UserDBTScheme.FIRST_NAME.toString(),u.getFirst_name().getBytes());
	  record.put(UserDBTScheme.LAST_NAME.toString(),u.getLast_name().getBytes());
	  record.put(UserDBTScheme.PASSWORD.toString(),u.getPass().getBytes());
	  record.put(UserDBTScheme.PHONE.toString(),u.getPhone().getBytes());
	  if(u.getPhoto() != null){
	    long len = u.getPhoto().length();
	    record.put(UserDBTScheme.PHOTO.toString(),u.getPhoto().getBytes(1, (int)len));
	  }
	  else{
	    record.put(UserDBTScheme.PHOTO.toString(),null);
	  }
	  record.put(UserDBTScheme.ROLE.toString(),u.getRole().getBytes());
	  record.put(UserDBTScheme.USER_NAME.toString(),u.getName().getBytes());
	  updateUserMap(con, record);
  }
  
  private void updateUserMap(Connection con,Map<String, byte[]> record) throws SQLException, ClassNotFoundException, ItemAlreadyInDBException {
	    Utility.checkIfNull(record);
	    // Trying to establish a connection with the DB
    // We obtain a submap from the passes one, that contains only the
    // key mappings
    String insertItems = parseUpdateColumns(record);
    List<byte[]> insertValues = parseInsertValues(record);
    //String insert = "INSERT INTO " + dbName + " " + insertItems;
    //Statement stmt = con.createStatement();
    java.sql.PreparedStatement pstmt = con.prepareStatement("UPDATE " + dbName + " SET " + insertItems + " WHERE " +
    UserDBTScheme.USER_NAME.toString()+"=?");
    int i=0;
    for(; i< insertValues.size(); i++)
      pstmt.setBytes(i+1, insertValues.get(i));
    pstmt.setBytes(i+1, record.get(UserDBTScheme.USER_NAME.toString()));
    pstmt.executeUpdate();
	  }
}
